﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using QuanLyKho.Common;

namespace QuanLyKho.DataAccess
{
   public class clsDataAccessListDistrict
    {
        private SqlConnection conn = new clsConnect().GetSqlConnection();
        private SqlCommand cmd = null;
        private SqlDataReader dr = null;
        private StringBuilder sBuilder = null;

        #region iAction District
        public int AddNew(object objParam)
        {
            try
            {
                int status = 0;
                conn.Open();
                clsDistrict obj = (clsDistrict)objParam;
                sBuilder = new StringBuilder("Sp_listDistrict_Add");
                cmd = new SqlCommand(sBuilder.ToString(), conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Code", obj.Code);
                cmd.Parameters.AddWithValue("@Name", obj.Name);
                cmd.Parameters.AddWithValue("@ShortName", obj.ShortName);
                cmd.Parameters.AddWithValue("@Des", obj.Des);             
                cmd.Parameters.AddWithValue("@IDListProvince", obj.IDListProvince);
                status = cmd.ExecuteNonQuery();
                conn.Close();
               return status;
            }
            catch (Exception ex)
            { throw ex; }
            finally
            { conn.Close();}
        }

        public int Update(object objParam)
        {
            try
            {
                int status = 0;
                conn.Open();
                clsDistrict obj = (clsDistrict)objParam;
                sBuilder = new StringBuilder("Sp_listDistrict_Update");
                cmd = new SqlCommand(sBuilder.ToString(), conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ID", obj.ID);
                cmd.Parameters.AddWithValue("@Code", obj.Code);
                cmd.Parameters.AddWithValue("@Name", obj.Name);
                cmd.Parameters.AddWithValue("@Des", obj.Des);
                cmd.Parameters.AddWithValue("@ShortName", obj.ShortName);
                cmd.Parameters.AddWithValue("@IDListProvince", obj.IDListProvince);
                status = cmd.ExecuteNonQuery();
                conn.Close();
                return status;
            }
            catch (Exception ex)
            { throw ex; }
            finally
            { conn.Close(); }
        }

        public int Delete(long id)
        {
            try
            {
                int status = 0;
                conn.Open();
                sBuilder = new StringBuilder("Sp_listDistrict_Delete");
                cmd = new SqlCommand(sBuilder.ToString(), conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ID", id);
                status = cmd.ExecuteNonQuery();
                conn.Close();
                return status;
            }
            catch (Exception ex)
            { throw ex; }
            finally
            { conn.Close(); }
        }

        #endregion

        public List<clsDistrict> ListDistrictSelectAll()
        {
            try
            {
                List<clsDistrict> list = new List<clsDistrict>();
                sBuilder = new StringBuilder("Sp_listDistrict_SelectAll");
                conn.Open();
                cmd = new SqlCommand(sBuilder.ToString(), conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        clsDistrict obj = new clsDistrict();
                        if (dr["ID"] != DBNull.Value)
                            obj.ID = Convert.ToInt64(dr["ID"]);
                        if (dr["Code"] != DBNull.Value)
                            obj.Code = Convert.ToString(dr["Code"]);
                        if (dr["Name"] != DBNull.Value)
                            obj.Name = Convert.ToString(dr["Name"]);
                        if (dr["ShortName"] != DBNull.Value)
                            obj.ShortName = Convert.ToString(dr["ShortName"]);
                        if (dr["Des"] != DBNull.Value)
                            obj.Des = Convert.ToString(dr["Des"]);
                        if (dr["IDListProvince"] != DBNull.Value)
                            obj.IDListProvince = Convert.ToInt64(dr["IDListProvince"]);
                        list.Add(obj);
                    }
                }
                conn.Close();
                return list;
            }
            catch (Exception ex)
            { throw ex; }
            finally
            { conn.Close(); }
        }
       /// <summary>
       /// Lấy danh sách Tất cả thông tin huyện và ID và Tên Tỉnh
       /// </summary>
       /// <returns></returns>

        public List<clsDistrict> ListDistrictProvinceSelectAll()
        {
            try
            {
                List<clsDistrict> list = new List<clsDistrict>();
                sBuilder = new StringBuilder("Sp_listDistric_Province_SelectAll");
                conn.Open();
                cmd = new SqlCommand(sBuilder.ToString(), conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        clsDistrict obj = new clsDistrict();
                        if (dr["ID"] != DBNull.Value)
                            obj.ID = Convert.ToInt64(dr["ID"]);
                        if (dr["Code"] != DBNull.Value)
                            obj.Code = Convert.ToString(dr["Code"]);
                        if (dr["Name"] != DBNull.Value)
                            obj.Name = Convert.ToString(dr["Name"]);
                        if (dr["ShortName"] != DBNull.Value)
                            obj.ShortName = Convert.ToString(dr["ShortName"]);
                        if (dr["Des"] != DBNull.Value)
                            obj.Des = Convert.ToString(dr["Des"]);
                        if (dr["IDListProvince"] != DBNull.Value)
                            obj.IDListProvince = Convert.ToInt64(dr["IDListProvince"]);
                        if (dr["Id"] != DBNull.Value)
                            obj.Id = Convert.ToInt64(dr["Id"]);
                        if (dr["NameProvince"] != DBNull.Value)
                            obj.Nameprovince = Convert.ToString(dr["NameProvince"]);
                        list.Add(obj);
                    }
                }
                conn.Close();
                return list;
            }
            catch (Exception ex)
            { throw ex; }
            finally
            { conn.Close(); }
        }

        public List<clsDistrict> ListDistrictSelectOneWithId(long id)
        {
            try
            {
                List<clsDistrict> list = new List<clsDistrict>();
                sBuilder = new StringBuilder("Sp_listDistrict_SelectOne");
                conn.Open();
                cmd = new SqlCommand(sBuilder.ToString(), conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ID", id);
                dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        clsDistrict obj = new clsDistrict();
                        if (dr["ID"] != DBNull.Value)
                            obj.ID = Convert.ToInt64(dr["ID"]);
                        if (dr["Code"] != DBNull.Value)
                            obj.Code = Convert.ToString(dr["Code"]);
                        if (dr["Name"] != DBNull.Value)
                            obj.Name = Convert.ToString(dr["Name"]);
                        if (dr["ShortName"] != DBNull.Value)
                            obj.ShortName = Convert.ToString(dr["ShortName"]);
                        if (dr["Des"] != DBNull.Value)
                            obj.Des = Convert.ToString(dr["Des"]);
                        if (dr["IDListProvince"] != DBNull.Value)
                            obj.IDListProvince = Convert.ToInt64(dr["IDListProvince"]);
                        list.Add(obj);
                    }
                }
                conn.Close();
                return list;
            }
            catch (Exception ex)
            { throw ex; }
            finally
            { conn.Close(); }
        }


        public List<clsDistrict> ListDistrictProvince_SelectOneWithId(long id)
        {
            try
            {
                List<clsDistrict> list = new List<clsDistrict>();
                sBuilder = new StringBuilder("Sp_listDistric_Province_SelectOne");
                conn.Open();
                cmd = new SqlCommand(sBuilder.ToString(), conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ID", id);
                dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        clsDistrict obj = new clsDistrict();
                        if (dr["ID"] != DBNull.Value)
                            obj.ID = Convert.ToInt64(dr["ID"]);
                        if (dr["Code"] != DBNull.Value)
                            obj.Code = Convert.ToString(dr["Code"]);
                        if (dr["Name"] != DBNull.Value)
                            obj.Name = Convert.ToString(dr["Name"]);
                        if (dr["ShortName"] != DBNull.Value)
                            obj.ShortName = Convert.ToString(dr["ShortName"]);
                        if (dr["Des"] != DBNull.Value)
                            obj.Des = Convert.ToString(dr["Des"]);
                        if (dr["IDListProvince"] != DBNull.Value)
                            obj.IDListProvince = Convert.ToInt64(dr["IDListProvince"]);
                        if (dr["ID"] != DBNull.Value)
                            obj.Id = Convert.ToInt64(dr["ID"]);
                        if (dr["NameProvince"] != DBNull.Value)
                            obj.Nameprovince = Convert.ToString(dr["NameProvince"]);
                        list.Add(obj);
                    }
                }
                conn.Close();
                return list;
            }
            catch (Exception ex)
            { throw ex; }
            finally
            { conn.Close(); }
        }



       

    }
}
